--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_AMOUNT_PRO_DEPB.HQL
--    Functions : 表11：存款余额产品额度结构表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-12  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_AMOUNT_PRO_DEPB PARTITION (DATA_DATE = '#V_DATA_DATE#')
SELECT  AREA.AREA_CODE_4
       ,XT.CCY
       ,XT.DEPOSIT_TYPE                    -- 产品类别
       ,XT.TYPE_DESC                       -- 产品类别描述
       ,SUM(XT.BAL_0_1W      )/100000000   -- 存款余额_0到1万元
       ,SUM(XT.WSUM_0_1W     )/100000000   -- 存款余额加权值_0_1万元
       ,SUM(XT.BAL_1_5W      )/100000000   -- 存款余额_1到5万元
       ,SUM(XT.WSUM_1_5W     )/100000000   -- 存款余额加权值_1_5万元
       ,SUM(XT.BAL_5_10W     )/100000000   -- 存款余额_5到10万元
       ,SUM(XT.WSUM_5_10W    )/100000000   -- 存款余额加权值_5_10万元
       ,SUM(XT.BAL_10_20W    )/100000000   -- 存款余额_10到20万元
       ,SUM(XT.WSUM_10_20W   )/100000000   -- 存款余额加权值_10_20万元
       ,SUM(XT.BAL_20_50W    )/100000000   -- 存款余额_20到50万元
       ,SUM(XT.WSUM_20_50W   )/100000000   -- 存款余额加权值_20_50万元
       ,SUM(XT.BAL_50_100W   )/100000000   -- 存款余额_50到100万元
       ,SUM(XT.WSUM_50_100W  )/100000000   -- 存款余额加权值_50_100万元
       ,SUM(XT.BAL_100_9999W )/100000000   -- 存款余额_100万元以上
       ,SUM(XT.WSUM_100_9999W)/100000000   -- 存款余额加权值_100万元以上
       ,SUM(XT.BAL_ALL       )/100000000   -- 存款余额_所有额度
       ,SUM(XT.WSUM_ALL      )/100000000   -- 存款余额加权值_所有额度
FROM (
    -- 个人+机构
    SELECT
         DEP.FIN_ORG_NO                                                                  AS FIN_ORG_NO
        ,DEP.CCY                                                                         AS CCY
        ,DEP.CLIENT_TYPE                                                                 AS DEPOSIT_TYPE
        ,CASE WHEN DEP.CLIENT_TYPE='1' THEN '个人'
              WHEN DEP.CLIENT_TYPE='0' THEN '机构' END                                   AS TYPE_DESC
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_0_1W        -- 存款余额_0到1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_0_1W       -- 存款余额加权值_0_1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_1_5W        -- 存款余额_1到5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_1_5W       -- 存款余额加权值_1_5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_5_10W       -- 存款余额_5到10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_5_10W      -- 存款余额加权值_5_10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_10_20W      -- 存款余额_10到20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_10_20W     -- 存款余额加权值_10_20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_20_50W      -- 存款余额_20到50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_20_50W     -- 存款余额加权值_20_50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_50_100W     -- 存款余额_50到100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_50_100W    -- 存款余额加权值_50_100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_100_9999W   -- 存款余额_100万元以上
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_100_9999W  -- 存款余额加权值_100万元以上
        -- 所有额度下含 活期存款D013
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                      AS BAL_ALL         -- 存款余额_所有额度
        ,COALESCE(DEP.WSUM_BAL,0)                                                        AS WSUM_ALL        -- 存款余额加权值_所有额度
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#')DEP


    UNION ALL

    -- 个人_活期储蓄存款+定期储蓄存款+定活两便存款+通知存款+协议存款
    SELECT
         DEP.FIN_ORG_NO                                                                       AS FIN_ORG_NO
        ,DEP.CCY                                                                              AS CCY
        ,CASE WHEN DEP.DEPOSIT_TYPE='D013' THEN '11'
              WHEN DEP.DEPOSIT_TYPE='D014' THEN '12'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '13'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '14'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '15'
         END                                                                                  AS DEPOSIT_TYPE
        ,CASE WHEN DEP.DEPOSIT_TYPE='D013' THEN '活期储蓄存款'
              WHEN DEP.DEPOSIT_TYPE='D014' THEN '定期储蓄存款'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '定活两便存款'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '通知存款'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '协议存款'
         END                                                                                  AS TYPE_DESC
         -- 不加工活期储蓄存款D013
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_0_1W        -- 存款余额_0到1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_0_1W       -- 存款余额加权值_0_1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_1_5W        -- 存款余额_1到5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_1_5W       -- 存款余额加权值_1_5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_5_10W       -- 存款余额_5到10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_5_10W      -- 存款余额加权值_5_10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_10_20W      -- 存款余额_10到20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_10_20W     -- 存款余额加权值_10_20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_20_50W      -- 存款余额_20到50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_20_50W     -- 存款余额加权值_20_50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_50_100W     -- 存款余额_50到100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_50_100W    -- 存款余额加权值_50_100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_100_9999W   -- 存款余额_100万元以上
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D014') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_100_9999W  -- 存款余额加权值_100万元以上
        -- 所有额度下含活期存款D013
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                                                                         AS BAL_ALL         -- 存款余额_所有额度
        ,COALESCE(DEP.WSUM_BAL,0)                                                                                                           AS WSUM_ALL        -- 存款余额加权值_所有额度
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND CLIENT_TYPE = '1' AND DEPOSIT_TYPE IN ('D013','D02','D03','D04','D014'))DEP

    UNION ALL

    -- 个人+机构_协定存款
    SELECT
         DEP.FIN_ORG_NO                                                                       AS FIN_ORG_NO
        ,DEP.CCY                                                                              AS CCY
        ,CASE WHEN DEP.CLIENT_TYPE='1' THEN '16'
              WHEN DEP.CLIENT_TYPE='0' THEN '26'
         END                                                                                  AS DEPOSIT_TYPE
        ,'协定存款'                                                                           AS TYPE_DESC
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_0_1W        -- 存款余额_0到1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_0_1W       -- 存款余额加权值_0_1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_1_5W        -- 存款余额_1到5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_1_5W       -- 存款余额加权值_1_5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_5_10W       -- 存款余额_5到10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_5_10W      -- 存款余额加权值_5_10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_10_20W      -- 存款余额_10到20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_10_20W     -- 存款余额加权值_10_20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_20_50W      -- 存款余额_20到50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_20_50W     -- 存款余额加权值_20_50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_50_100W     -- 存款余额_50到100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_50_100W    -- 存款余额加权值_50_100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_100_9999W   -- 存款余额_100万元以上
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_100_9999W  -- 存款余额加权值_100万元以上
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                           AS BAL_ALL         --所有期限-存款余额
        ,COALESCE(DEP.WSUM_BAL,0)                                                             AS WSUM_ALL        --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN ('D051','D052'))DEP

    UNION ALL

    -- 个人+机构_保证金存款
    SELECT
         DEP.FIN_ORG_NO                                                                       AS FIN_ORG_NO
        ,DEP.CCY                                                                              AS CCY
        ,CASE WHEN CLIENT_TYPE='1' THEN '17'
              WHEN CLIENT_TYPE='0' THEN '27'
        END                                                                                   AS DEPOSIT_TYPE
        ,'保证金贷款'                                                                         AS TYPE_DESC
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_0_1W        -- 存款余额_0到1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_0_1W       -- 存款余额加权值_0_1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_1_5W        -- 存款余额_1到5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_1_5W       -- 存款余额加权值_1_5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_5_10W       -- 存款余额_5到10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_5_10W      -- 存款余额加权值_5_10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_10_20W      -- 存款余额_10到20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_10_20W     -- 存款余额加权值_10_20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_20_50W      -- 存款余额_20到50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_20_50W     -- 存款余额加权值_20_50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_50_100W     -- 存款余额_50到100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_50_100W    -- 存款余额加权值_50_100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_100_9999W   -- 存款余额_100万元以上
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_100_9999W  -- 存款余额加权值_100万元以上
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                           AS BAL_ALL         --所有期限-存款余额
        ,COALESCE(DEP.WSUM_BAL,0)                                                             AS WSUM_ALL        --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069'))DEP

    UNION ALL

    -- 机构_单位活期存款+单位定期存款+定活两便存款+通知存款+协议存款
    SELECT
         DEP.FIN_ORG_NO                                                                       AS FIN_ORG_NO
        ,DEP.CCY                                                                              AS CCY
        ,CASE WHEN DEP.DEPOSIT_TYPE='D011' THEN '11'
              WHEN DEP.DEPOSIT_TYPE='D012' THEN '12'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '13'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '14'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '15'
         END                                                                                  AS DEPOSIT_TYPE
        ,CASE WHEN DEP.DEPOSIT_TYPE='D011' THEN '单位活期存款'
              WHEN DEP.DEPOSIT_TYPE='D012' THEN '单位定期存款'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '定活两便存款'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '通知存款'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '协议存款'
         END                                                                                  AS TYPE_DESC
         -- 不加工 单位活期存款D011
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_0_1W        -- 存款余额_0到1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_0_1W       -- 存款余额加权值_0_1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_1_5W        -- 存款余额_1到5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_1_5W       -- 存款余额加权值_1_5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_5_10W       -- 存款余额_5到10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_5_10W      -- 存款余额加权值_5_10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_10_20W      -- 存款余额_10到20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_10_20W     -- 存款余额加权值_10_20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_20_50W      -- 存款余额_20到50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_20_50W     -- 存款余额加权值_20_50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_50_100W     -- 存款余额_50到100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_50_100W    -- 存款余额加权值_50_100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)  AS BAL_100_9999W   -- 存款余额_100万元以上
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' AND DEP.DEPOSIT_TYPE IN ('D02','D03','D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)  AS WSUM_100_9999W  -- 存款余额加权值_100万元以上
        -- 所有额度下含 单位活期存款D011
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                                                                         AS BAL_ALL         -- 存款余额_所有额度
        ,COALESCE(DEP.WSUM_BAL,0)                                                                                                           AS WSUM_ALL        -- 存款余额加权值_所有额度
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND CLIENT_TYPE = '0'AND DEPOSIT_TYPE IN ('D011','D012','D02','D03','D04'))DEP

    UNION ALL

    -- 个人 机构 合计
    SELECT
         DEP.FIN_ORG_NO                                                                       AS FIN_ORG_NO
        ,DEP.CCY                                                                              AS CCY
        ,'3'                                                                                  AS DEPOSIT_TYPE
        ,'合计'                                                                               AS TYPE_DESC
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_0_1W        -- 存款余额_0到1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A000' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_0_1W       -- 存款余额加权值_0_1万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_1_5W        -- 存款余额_1到5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A001' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_1_5W       -- 存款余额加权值_1_5万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_5_10W       -- 存款余额_5到10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A005' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_5_10W      -- 存款余额加权值_5_10万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_10_20W      -- 存款余额_10到20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A010' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_10_20W     -- 存款余额加权值_10_20万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_20_50W      -- 存款余额_20到50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A020' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_20_50W     -- 存款余额加权值_20_50万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_50_100W     -- 存款余额_50到100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A050' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_50_100W    -- 存款余额加权值_50_100万元
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' THEN DEP.ACTUAL_BAL ELSE 0 END,0)       AS BAL_100_9999W   -- 存款余额_100万元以上
        ,COALESCE(CASE WHEN DEP.INTERVAL_CODE1='A100' THEN DEP.WSUM_BAL   ELSE 0 END,0)       AS WSUM_100_9999W  -- 存款余额加权值_100万元以上
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                           AS BAL_ALL        --所有期限-存款余额
        ,COALESCE(DEP.WSUM_BAL,0)                                                             AS WSUM_ALL        --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#')DEP
      )XT
  -- 与地区表最细级关联
  LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON XT.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
  LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
  GROUP BY  AREA.AREA_CODE_4
           ,XT.CCY
           ,XT.DEPOSIT_TYPE                 -- 产品类别
           ,XT.TYPE_DESC                    -- 产品类别描述
;